package com.F05.action;


import java.util.ArrayList;
import java.util.List;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import org.apache.log4j.Logger;
import org.apache.struts.action.ActionForm;
import org.apache.struts.action.ActionForward;
import org.apache.struts.action.ActionMapping;

import com.F03.actionform.F0303ActionForm;
import com.F05.actionform.F0506ActionForm;
import com.F05.dao.F0506DAO;
import com.action.BaseAction;
import com.config.Constants;
import com.info.T_S_YHCZXXinfo;
import com.util.ExcelExport;


/**
 * 
 * @file F0506Action.java
 * @author huqy
 * @since 2009-3-15
 * @version 1.0
 */
public class F0506Action extends BaseAction {

	/**
	 * Method execute
	 * @param mapping
	 * @param form
	 * @param request
	 * @param response
	 * @return ActionForward
	 */
	Logger log = Logger.getLogger(F0506Action.class);

	/** 操作记录INFO */
	T_S_YHCZXXinfo t_YhczxxInfo = null;
	
	public ActionForward execute(ActionMapping mapping, ActionForm form,
			HttpServletRequest request, HttpServletResponse response){
		F0506ActionForm actionForm = (F0506ActionForm) form;
		F0506DAO dao = new F0506DAO();
		String operationType = actionForm.getOperationType();
		String strList = "list";
		
		try {
			//操作记录
			t_YhczxxInfo = setYhczxx(request, response, operationType,"F0506");

			/*
			if(Constants.BACK.equals(operationType)){
				return mapping.findForward(strList);
			}
			*/

			if(Constants.QUERYLIST.equals(operationType)){
				doQueryList(actionForm,dao.queryList(actionForm),dao.queryTotalList(actionForm));
				//actionForm.setList(dao.queryList(actionForm));
				//actionForm.setTotalList(dao.queryTotalList(actionForm));
				return mapping.findForward(strList);
			}
			
			else if(Constants.INIT.equals(operationType)){
				return mapping.findForward(strList);
			}
			else if("export".equals(operationType)){
				doExport(actionForm, dao, response);
				return mapping.findForward("list");
			}
		} catch (Exception e){
			e.printStackTrace();
		} finally {
			if (dao != null) dao.disConnect();
		}
		return mapping.findForward(strList);
	}
	
	private void doQueryList(F0506ActionForm actionForm,List list,List totalList){
		if(list!=null&&list.size()>0){
			actionForm.setList(list);
			actionForm.setTotalList(totalList);
		}
		else{
			actionForm.setList(list);
			actionForm.setTotalList(totalList);
			actionForm.setErrorFlag("2");
		}	
	}
	
	private void doExport(F0506ActionForm actionForm, F0506DAO dao, HttpServletResponse response){
		List titleList = new ArrayList();
		try {
			String fileName = "发票统计";
			/* 标题设定 */
			titleList.add("订单号码");
			titleList.add("工作号");
			titleList.add("发票号码");
			titleList.add("客户名称");
			titleList.add("单价");
			titleList.add("数量");
			titleList.add("人民币");
			titleList.add("美元");
			titleList.add("欧元");
			titleList.add("开票时间");
			titleList.add("提前开票");
			titleList.add("是否自制");
			/* sql作成 */
			String sql = getSql(actionForm);
			String sqlTotal = getSqlTotal(actionForm);
			/* excel输出 */
			
			ExcelExport.exportTotal(fileName, titleList, sql,sqlTotal, response);

		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			if (dao != null)
				dao.disConnect();
		}
	}
	
	public String getSql(F0506ActionForm form){
		StringBuffer sql=new StringBuffer();
		sql.append(" select  contract_num,manufacturing_order,invoice_number,");
    	sql.append(" b.customers_name,unit_price,qty,total_rbm,total_usd, total_euros,");
        sql.append(" convert(varchar(10),invoice_date,120) as invoice_date,c.name as advanced_invoice_name,d.name as invoice_flag_name ");
        sql.append(" from f_invoicing_production a,c_customers b,b_whether c,b_whether d ");
        sql.append(" where a.customers_code = b.customers_code and a.advanced_invoice = c.code and a.invoice_flag = d.code ");
 
    	if(!form.getContractNum1().equals("")){
    		sql.append(" and a.contract_num >="+form.getContractNum1());
    	}
    	if(!form.getContractNum2().equals("")){
    		sql.append(" and a.contract_num <="+form.getContractNum2());
    	}
    	if(!form.getInvoiceDate1().equals("")){
    		sql.append(" and a.invoice_date >= convert(datetime,'"+form.getInvoiceDate1()+"')");
    	}
    	if(!form.getInvoiceDate2().equals("")){
    		sql.append(" and a.invoice_date <= convert(datetime,'"+form.getInvoiceDate2()+"')");
    	}
    	if(!form.getManufacturingOrder1().equals("")){
    		sql.append(" and a.manufacturing_order >='"+form.getManufacturingOrder1()+"' ");
    	}
    	if(!form.getManufacturingOrder2().equals("")){
    		 sql.append(" and a.manufacturing_order <='"+form.getManufacturingOrder2()+"' ");
    	}
    	if(!form.getInvoiceNumber1().equals("")){
    		sql.append(" and a.invoice_number >='"+form.getInvoiceNumber1()+"' ");
    	}
    	if(!form.getInvoiceNumber2().equals("")){
    		sql.append(" and a.invoice_number <='"+form.getInvoiceNumber2()+"' ");
    	}
    	if(!form.getAdvancedInvoice().equals("")){
    		sql.append(" and a.advanced_invoice = '"+form.getAdvancedInvoice()+"'");
    	}
    	if(!form.getInvoiceFlag().equals("")){
    		sql.append(" and a.invoice_flag = '"+form.getInvoiceFlag()+"'");
    	}
		return sql.toString();
	}
	
	public String getSqlTotal(F0506ActionForm form){
		StringBuffer sql=new StringBuffer();
		sql.append(" select '总计',count(*) as countNum,'',''," );
		sql.append(" sum(convert(float,unit_price)) as totalUnitPrice,sum(qty) as totalQty,sum(convert(float,total_rbm)) as totalRbm,");
    	sql.append(" sum(convert(float,total_usd)) as totalUsd, sum(convert(float,total_euros)) as totalEuros,'','','' ");
        sql.append(" from f_invoicing_production ");
        sql.append(" where 1=1 ");
 
    	if(!form.getContractNum1().equals("")){
    		sql.append(" and contract_num >="+form.getContractNum1());
    	}
    	if(!form.getContractNum2().equals("")){
    		sql.append(" and contract_num <="+form.getContractNum2());
    	}
    	if(!form.getInvoiceDate1().equals("")){
    		sql.append(" and invoice_date >= convert(datetime,'"+form.getInvoiceDate1()+"')");
    	}
    	if(!form.getInvoiceDate2().equals("")){
    		sql.append(" and invoice_date <= convert(datetime,'"+form.getInvoiceDate2()+"')");
    	}
    	if(!form.getManufacturingOrder1().equals("")){
    		sql.append(" and manufacturing_order >='"+form.getManufacturingOrder1()+"' ");
    	}
    	if(!form.getManufacturingOrder2().equals("")){
    		 sql.append(" and manufacturing_order <='"+form.getManufacturingOrder2()+"' ");
    	}
    	if(!form.getInvoiceNumber1().equals("")){
    		sql.append(" and invoice_number >='"+form.getInvoiceNumber1()+"' ");
    	}
    	if(!form.getInvoiceNumber2().equals("")){
    		sql.append(" and invoice_number <='"+form.getInvoiceNumber2()+"' ");
    	}
    	if(!form.getAdvancedInvoice().equals("")){
    		sql.append(" and advanced_invoice = '"+form.getAdvancedInvoice()+"'");
    	}
    	if(!form.getInvoiceFlag().equals("")){
    		sql.append(" and invoice_flag = '"+form.getInvoiceFlag()+"'");
    	}
		return sql.toString();
	}

}
